/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package junchangmis.student;

/**
 *
 * @author developers
 */
import java.util.logging.Level;
import java.util.logging.Logger;
import publicmethod.*;
import junchangmis.codecSetting.*;

import java.util.StringTokenizer;
import java.io.File;
import java.io.IOException;
import java.util.Locale;

import jxl.CellView;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Alignment;
import jxl.format.UnderlineStyle;
import jxl.write.Formula;
import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

import java.sql.*;
import java.util.Vector;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.PageOrientation;
import jxl.format.VerticalAlignment;

public class dutyTableExcelA {

    private WritableCellFormat timesBoldUnderline;
    private WritableCellFormat times;
    private WritableCellFormat timesOne;
    private WritableCellFormat timesTwo;
    private WritableCellFormat timesTop;
    private WritableCellFormat timesBoldHead;
    private String inputFile;
    String classCode;
    String type;
    String meaning;

    public dutyTableExcelA(String classCode, String type) throws WriteException, IOException {
        this.classCode = classCode;
        this.type = type;
        String codeTriple = codecMethodNew.getMeaning(classCode);//获得总编码的翻译结果
        meaning = codeTriple;
    }

    public void setOutputFile(String inputFile) {
        this.inputFile = inputFile;
    }

    public void write() throws IOException, WriteException {
        File file = new File(inputFile);
        WorkbookSettings wbSettings = new WorkbookSettings();

        wbSettings.setLocale(new Locale("en", "CN"));

        WritableWorkbook workbook = Workbook.createWorkbook(file, wbSettings);
        workbook.createSheet("Report", 0);
        WritableSheet excelSheet = workbook.getSheet(0);
        //设置表格打印属性
        excelSheet.getSettings().setTopMargin(0.3);
        excelSheet.getSettings().setLeftMargin(0.1);
        excelSheet.getSettings().setRightMargin(0.1);
        excelSheet.getSettings().setBottomMargin(0.1);
        excelSheet.getSettings().setHorizontalCentre(true);
        excelSheet.getSettings().setOrientation(PageOrientation.LANDSCAPE);

        createLabel(excelSheet);
        createContent(excelSheet);

        workbook.write();
        workbook.close();
    }

    private void createLabel(WritableSheet sheet)
            throws WriteException {
        WritableFont times16ptBoldHead = new WritableFont(WritableFont.TIMES, 18);
        timesBoldHead = new WritableCellFormat(times16ptBoldHead);
        timesBoldHead.setAlignment(Alignment.CENTRE);
        timesBoldHead.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesBoldHead.setWrap(true);

        WritableFont times10pt = new WritableFont(WritableFont.TIMES, 11);
        times = new WritableCellFormat(times10pt);
        times.setAlignment(Alignment.CENTRE);
        times.setVerticalAlignment(VerticalAlignment.CENTRE);
        times.setBorder(Border.ALL, BorderLineStyle.THIN);
        times.setWrap(true);

        timesOne = new WritableCellFormat(times10pt);
        timesOne.setAlignment(Alignment.LEFT);
        timesOne.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesOne.setBorder(Border.LEFT, BorderLineStyle.THIN);
        timesOne.setBorder(Border.TOP, BorderLineStyle.THIN);
        timesOne.setBorder(Border.RIGHT, BorderLineStyle.THIN);
        timesOne.setWrap(true);

        timesTop = new WritableCellFormat(times10pt);
        timesTop.setAlignment(Alignment.LEFT);
        timesTop.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesTop.setWrap(true);

        timesTwo = new WritableCellFormat(times10pt);
        timesTwo.setAlignment(Alignment.LEFT);
        timesTwo.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesTwo.setBorder(Border.LEFT, BorderLineStyle.THIN);
        timesTwo.setBorder(Border.BOTTOM, BorderLineStyle.THIN);
        timesTwo.setBorder(Border.RIGHT, BorderLineStyle.THIN);
        timesTwo.setWrap(true);


        WritableFont times10ptBoldUnderline = new WritableFont(
                WritableFont.TIMES, 11, WritableFont.BOLD, false);
        timesBoldUnderline = new WritableCellFormat(times10ptBoldUnderline);
        timesBoldUnderline.setAlignment(Alignment.CENTRE);
        timesBoldUnderline.setVerticalAlignment(VerticalAlignment.CENTRE);
        timesBoldUnderline.setBorder(Border.ALL, BorderLineStyle.MEDIUM);
        timesBoldUnderline.setWrap(true);

        CellView cv = new CellView();
        cv.setFormat(times);
        cv.setFormat(timesBoldUnderline);
        cv.setAutosize(true);

        // Write a few headers

        sheet.mergeCells(0, 0, 28, 0);
        addHeader(sheet, 0, 0, "军昌文化艺术学校学生考勤表（" + type + "表）");
        sheet.setRowView(0, 700);
        sheet.mergeCells(0, 1, 28, 1);
        sheet.setRowView(1, 300);
        addLabelTop(sheet, 0, 1, "班级编码：" + classCode + "（" + meaning + "）" + "        " + "上课时间：                                     " + "每次课收费：                                        ");

        sheet.mergeCells(0, 2, 28, 2);
        sheet.setRowView(2, 300);
        addLabelTop(sheet, 0, 2, "教师：                                                              " + "管理人：                                         " + "本级开学时间：                                       " + "初始开班时间：");

        sheet.mergeCells(0, 3, 0, 4);
        addCaption(sheet, 0, 3, "学生电话");
        sheet.mergeCells(1, 3, 1, 4);
        addCaption(sheet, 1, 3, "学生编码");
        sheet.mergeCells(2, 3, 2, 4);
        addCaption(sheet, 2, 3, "学生姓名");
        sheet.mergeCells(3, 3, 3, 4);
        addCaption(sheet, 3, 3, "结转课次");
        sheet.mergeCells(4, 3, 4, 4);
        addCaption(sheet, 4, 3, "新交课次");

        sheet.setColumnView(0, 12);
        sheet.setColumnView(1, 12);

        for (int i = 5; i < 29; i++) {
            sheet.setColumnView(i, 4);
        }
        for (int i = 5; i < 29; i++) {
            addCaption(sheet, i, 3, "");
            addCaption(sheet, i, 4, "");
        }
    }

    private void createContent(WritableSheet sheet) throws WriteException,
            RowsExceededException {
        try {
            Vector<String> name = new Vector<String>();
            Vector<String> id = new Vector<String>();
            Vector<String> tel = new Vector<String>();
            Vector<String> weekCount = new Vector<String>();
            Vector<String> coursePerWeek = new Vector<String>();

            DbOperation db = new DbOperation();
            db.DbConnect();
            String sql = "select * from formalStu where classCode='" + classCode + "'";
            ResultSet rs = db.DBSqlQuery(sql);
            while (rs.next()) {
                name.add(rs.getString("name"));
                id.add(rs.getString("id"));
                tel.add(rs.getString("tel"));
                weekCount.add(rs.getString("weekCount"));
                coursePerWeek.add(rs.getString("coursePerWeek"));
            }
            db.DbClose();
            for (int i = 0; i < name.size(); i++) {
                addLabel(sheet, 0, i + 5, tel.get(i));
                addLabel(sheet, 1, i + 5, id.get(i));
                addLabel(sheet, 2, i + 5, name.get(i));
                for (int j = 3; j < 29; j++) {
                    addLabel(sheet, j, i + 5, "");
                }
                int sum = Integer.valueOf(weekCount.get(i)) * Integer.valueOf(coursePerWeek.get(i));
                addLabel(sheet, 4, i + 5, sum + "");
            }
            sheet.mergeCells(0, name.size() + 5, 28, name.size() + 5);
            addLabelOne(sheet, 0, name.size() + 5, "每月到课人数合计：     月     人；     月     人；     月     人；     月     人；     月     人；");
            sheet.mergeCells(0, name.size() + 6, 28, name.size() + 6);
            String count = "一";
            addLabelTwo(sheet, 0, name.size() + 6, "说明：1.本表适用于每周" + count + "次课班  2.任课老师填写，管理人员核实  3.管理人员每月将人数合计报送工资考核人");

            for (int i = 1; i <= name.size() + 6; i++) {
                sheet.setRowView(i, 400);//内容为28行
            }
        } catch (SQLException ex) {
            Logger.getLogger(newDutyTableExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void addHeader(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldHead);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addCaption(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesBoldUnderline);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelTop(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesTop);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelOne(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesOne);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addLabelTwo(WritableSheet sheet, int column, int row, String s)
            throws RowsExceededException, WriteException {
        Label label;
        label = new Label(column, row, s, timesTwo);//位置 内容 格式
        sheet.addCell(label);
    }

    private void addNumber(WritableSheet sheet, int column, int row,
            Integer integer) throws WriteException, RowsExceededException {
        Number number;
        number = new Number(column, row, integer, times);//位置 内容 格式
        sheet.addCell(number);
    }

    private void addLabel(WritableSheet sheet, int column, int row, String s)
            throws WriteException, RowsExceededException {
        Label label;
        label = new Label(column, row, s, times);
        sheet.addCell(label);
    }

    public static void main(String[] args) throws WriteException, IOException {
        newDutyTableExcel test = new newDutyTableExcel("SSY111");
        test.setOutputFile("lars.xls");
        test.write();
        System.out.println("Please check the result file under lars.xls ");
    }
}
